"""Header_Information
------------------------------------
    File_Name: student
    Author: RR9
    Date: 2024/10/09
    Description: 
------------------------------------
"""

from fastapi import APIRouter
from app.models.ob1 import (Student, Course)
from app.schemas.ob1 import StudentIn
from app.database.config import OTHER_DB_CONFIG
from app.utils.contexts import OracleContext
from app.utils.database import fetch_all_dict

router = APIRouter()

import traceback


@router.get('/cs', summary="oralce11 原生sql查询")
async def get_student():
    """oralce11 原生sql查询"""
    sql = f""" update y1 set name = :name,city=:city where id = :id """
    sql2 = f""" select * from y1 """
    args = {"name": "麻溜", "city": "湖南", "id": 2}
    with OracleContext(OTHER_DB_CONFIG['lmis_cs']).get_conn() as conn:
        print('cccc:::', conn)
        try:
            print(111)
            cur = conn.cursor()
            cur.execute(sql, args)
            row = fetch_all_dict(cur, sql2)
        except Exception as e:
            print(traceback.print_exc())
            print('出错了！！！', str(e))
            conn.rollback()
            raise e
        else:
            print(222)
            conn.commit()
        finally:
            print(333)
            conn.close()
        return {"status": 200, "data": row}




@router.get('/', summary="查询所有学生")
async def get_student():
    """查询所有学生"""
    # 列表模型对象 [<Student: 1>, <Student: 2>, <Student: 3>,...]
    students = await Student.all()
    return students


@router.post("/", summary="新增学生")
async def create_student(student_in: StudentIn):
    """新增学生"""
    c_student = student_in.dict()
    # 剔除多对多字段
    c_student.pop("courses")
    # 保存学生信息,并返回学生信息
    student = await Student.create(**c_student)
    # 保存学生多对多关联信息
    choose_courses = await Course.filter(id__in=student_in.courses)
    print("choose_courses:", choose_courses)
    # student.courses 指向关联表 student_courses
    await student.courses.add(*choose_courses)  # * 打散传递
    return student


@router.get(
    "/{student_id}/",
    summary="查看某一学生",
    # response_model=StudentOut,
    # response_model_include={"name", "sno", "clas_id"}
)
async def get_student(student_id: int):
    """查看某一学生"""
    # student = await Student.filter(id=student_id).values("courses__name")
    # student = await Student.filter(id=student_id).prefetch_related("courses").values("name")
    # await Student.filter(id=student_id).values_list()
    # 执行原生sql
    sql1 = f""" SELECT a.name,a.sno,b.name FROM student a, clas b where a.clas_id = b.id and a.id = '19' """
    # return student
    # async with connections.get('default') as conn:
    #     x = await conn.execute_query_dict(sql)
    #     print("xxxx:", x)
    # conn = connections.get('default')
    # x = await conn.execute_query_dict(sql)
    # print("xxxx:", x)
    return {"message": "查看某一学生"}


@router.put("/{student_id}/", summary="更新某一学生")
async def update_student(student_id: int):
    """更新某一学生"""
    return {"message": f"更新id = {student_id} 的学生"}


@router.delete("/{student_id}/", summary="删除某一学生")
async def delete_student(student_id: int):
    """删除某一学生"""
    return {"message": f"删除id = {student_id} 的学生"}
